MySQL对derived table的优化处理与使用限制
* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
前言
随着MySQL版本的发展，优化器是越来越智能，优化器开关也越来越多，本文给大家分享一下MySQL对derived table的优化处理。
何为derived table？这里我把它翻译成派生表，简单来讲，就是将from 子句中出现的检索结果集当做一张表，比如from 一个select构造的子查询，这个子查询就是一个派生表，from 一个视图，这个视图就是一个派生表，from 一个with 构造的临时表（Common table expression，CTE），这个CTE表就是一个派生表。
一、优化器对derived table的优化
优化器处理derived table有两种策略：1.将derived table合并到外查询块中，2，将derived table 物化为一个临时表。使用优化器开关derived_merge来控制优化器选择哪种策略。设置为on，选择策略1；设置为off，选择策略2。此开关从5.7.6版本时引入，默认值为on。
8.0.22版本中又引入优化器开关derived_condition_pushdown，默认值为on，表示外查询块中与派生表相关的条件会推入到派生表中，设置为off，则不会推入。
下面实验来说明，接下来本章节实验都是基于MySQL8.0.25。
实验表结构
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int DEFAULT NULL,
`c1` varchar(100) DEFAULT NULL,
KEY `idx_id` (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
实验语句：
select * from (select id,c1 from t1 ) dt where id=2;
（1）当前为两个开关都打开，此为默认值
mysql> set optimizer_switch="derived_merge=on,derived_condition_pushdown=on";
Query OK, 0 rows affected (0.00 sec)
从执行计划可以看出，select_type列没有DERIVED类型，说明派生表发生合并，相当于执行语句 select id,c1 from t1 where id=2合并后其实用不到下推了，下推关闭与否对执行计划没有影响。
（2）开启合并，关闭下推
可以看到对此语句而言，开启合并，不开启下推对执行计划没有影响。
（3）关闭合并，开启下推
mysql> set optimizer_switch="derived_merge=off,derived_condition_pushdown=on";
Query OK, 0 rows affected (0.00 sec)
select_type列出现DERIVED 类型, 说明派生表没有合并，派生表会物化为临时表，但此时外查询块中的条件推入到了派生表中，相当于执行语句 select * from (select id,c1 from t1 where id=2) dt，对过滤后的数据进行物化，先过滤再物化，访问的数据量小，物化结果集小。
（4）两个开关都关闭
mysql> set optimizer_switch="derived_merge=off,derived_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)
既不合并，又不下推，此时要对t1全表进行物化，再对物化结果集进行条件过滤。这种情况效率是最低的。
从上面的实验可以看出使用derived_merge, 可以避免不必要的物化，合并后，相当于将外查询块中的过滤条件直接推给了derived table。这样的执行计划更高效。那既然这样，下推的开关还有什么意义呢？
官方文档中有这样的说明：
When a derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer WHERE condition down to the derived table should decrease the number of rows that need to be processed and thus speed up execution of the query.
也就是说合并有限制时，条件下推到派生表就起了作用，减少物化数据的行数，这样能加速查询的执行。
那我们接下来研究一下派生合并的限制吧
二、derived merge的使用限制。
derived merge有限制的时候，往往是derived_condition_pushdown发挥作用的时候，但是也有一些限制对这两者都有影响。
1.派生表中含有max(),min(),count(),sum()等聚合函数，或者含有DISTINCT，GROUP BY,HAVING这些分组子句， 此时不会发生合并，但是外层查询的条件会推入派生表。这一点是derived_condition_pushdown主要发挥作用的地方。
例子1：外层查询对派生表中的聚合列做过滤，过滤条件会以having子句的形式推入到derived table。
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100;
优化器会将语句改写为：
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt;
例子2：外层查询对派生表的分组列做过滤，过滤条件会直接 推入派生表，减少derived table物化结果集的大小。
SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10
优化器会将语句改写为：
SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt;
2.派生表的select 列表中有子查询，也就是标量子查询, 此时不会合并，但是条件会下推入派生表。
举例：
select *
from (select stuno,
course_no,
(select course_name
from course c
where c.course_no = a.course_no) as course_name,
score
from score a) b
where b.stuno = 1;
上面这个例子中，因为派生表b的select 列表中有标量子查询 (select course_name from course c where c.course_no =a.course_no) as course_name，所以派生表 b 被物化，但是条件 stuno = 1 推入到派生表。
3.分配了用户变量，这种情况不发生合并，但是条件会下推入派生表。
select (@i := @i + 1) as rownum, stuno, course_no, course_name, score
from ((select a.stuno, a.course_no, b.course_name, a.score
from score a
left join course b
on a.course_no = b.course_no) dt, (select (@i := 0) num) c)
where stuno = 1;
上面这个例子使用用户变量的形式给记录加了行号，不能合并，但是可以将条件下推到派生表。
4.如果合并会导致外查询块中超过61张基表的连接访问，优化器会选择物化派生表。
这个其实不用关注，几乎没有语句对表的访问达到这个量级。
5.UNION 或union all，这种情况不会发生合并，在MySQL8.0.29版本之后条件会下推。
select id, c1
from (select id, c1 from t1
union
select id, c1 from t2) dt
where dt.id = 1;
8.0.25版本：
8.0.32版本：
6.对于视图而言，创建视图时如果指定了ALGORITHM=TEMPTABLE，它会阻止合并，这个属性的优先级比优化器开关的优先级要高。
7.派生表中含LIMIT子句，这种情况既不会合并，也不会条件下推，因为合并或条件下推后会导致结果集改变。
select * from (select id,c1 from t1 limit 10) a where a. id=1;
8.只引用了字面量值，这种情况不发生合并。
select * from (select '1' as c1, 2 as c2 ) a;
对于前4种情况，合并被阻止，但是条件下推可以发挥作用，第5种union和union all的这种需使用8.0.29及之后的版本才会条件下推到派生表。6，7既不能合并也不能下推，对于8这种情况，没有想到实际的应用场景。
三、derived_condition_pushdown的使用限制
1.MySQL8.0.29版本以前，派生表中使用了union，条件不能推入派生表，MySQL8.0.29及以后的版本没有此限制，前面已提及。
2.派生表中使用了limit ，条件不能推入派生表，前面已提及。
3.条件包含子查询，不能推入到派生表，但是会发生合并
select stuno, course_no, course_name, score
from (select a.stuno, a.course_no, b.course_name, a.score
from score a
left join course b
on a.course_no = b.course_no) dt
where stuno = (select distinct id from t1 where c1='gg张三')
4.条件是一个带参数的表达式，无法推入派生表。
5.派生表作为外连接的内层表(比如left join的右表)，条件无法推入到派生表，因为条件推入后，语句含义就变了。
6.从MySQL8.0.28版本开始，派生表的select 列表中包含分配的用户变量，条件不能推入派生表。
7.如果物化的派生表是一个Common Table Expression(CTE表），也就是使用with as构造的表，如果这个表被引用多次，则条件不能推入到派生表。
8.如果派生表是一个视图，视图创建时使用了ALGORITHM=TEMPTABLE，则条件不会推入进视图。
四、dervied_merge使用注意事项
如果满足以下三个条件，优化器会将derived table中的order by子句延迟到合并后的查询中执行。
(1)外层查询中没有分组或聚合运算
(2)外层查询没有指定distinct，having 或order by。
(3)外层查询中只有这个派生表作为from子句的唯一源。
这三个条件任何一项不满足，derived table中的order by子句将会被忽略。
举个例子：
--学生成绩表
create table score(stuno int,course_no varchar(10),score double(6,1),index idx_courseno(course_no),index idx_stuno(stuno));
insert into score values(1,'yw',97),(1,'sx',100),(1,'yy',70);
insert into score values(2,'yw',90),(2,'sx',90),(2,'yy',80);
insert into score values(3,'yw',89),(3,'sx',99),(3,'yy',90);
insert into score values(4,'yw',98),(4,'sx',88),(4,'yy',87);
--科目表
create table course(course_no varchar(10),course_name varchar(20),teacher varchar(20),note varchar(100),index idx_courseno(course_no));
insert into course values('yw','语文','Jenny','aaa');
insert into course values('sx','数学','Tony','bbb');
insert into course values('yy','英语','Richard','ccc');
--执行语句:查询学生语文学科的成绩，并按成绩倒序排列。
mysql> select a.stuno, b.course_name, a.score, b.teacher
-> from (select stuno, course_no, score
-> from score
-> where course_no = 'yw'
-> order by score desc) a
-> inner join course b
-> on a.course_no = b.course_no;
+-------+-------------+-------+---------+
| stuno | course_name | score | teacher |
+-------+-------------+-------+---------+
| 1 | 语文 | 97.0 | Jenny |
| 2 | 语文 | 90.0 | Jenny |
| 3 | 语文 | 89.0 | Jenny |
| 4 | 语文 | 98.0 | Jenny |
+-------+-------------+-------+---------+
4 rows in set (0.00 sec)
虽然派生表中有对score列进行排序，但结果集中明显score列是乱序的，也就是order by 子句被忽略了。之所以被忽略，就是没有满足第三个条件，外层查询from子句中不只有派生表a，还有course表。
执行计划如下：执行计划中没有derived table 表a，说明发生了合并。
展示一下warning的信息。
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`score`.`stuno` AS `stuno`,`test`.`b`.`course_name` AS `course_name`,`test`.`score`.`score` AS `score`,`test`.`b`.`teacher` AS `teacher` from `test`.`score` join `test`.`course` `b` where ((`test`.`b`.`course_no` = 'yw') and (`test`.`score`.`course_no` = 'yw')) |
+-------+------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
合并后相当于执行语句：
select a.stuno,b.course_name,a.score,b.teacher
from score a,course b
where a.course_no='yw' and b.course_no='yw';
如果禁止derived table合并,order by子句就不会被忽略，语句的结果就是正确的。
mysql> select /*+no_merge(a) */a.stuno, b.course_name, a.score, b.teacher
-> from (select stuno, course_no, score
-> from score
-> where course_no = 'yw'
-> order by score desc) a
-> inner join course b
-> on a.course_no = b.course_no;
+-------+-------------+-------+---------+
| stuno | course_name | score | teacher |
+-------+-------------+-------+---------+
| 4 | 语文 | 98.0 | Jenny |
| 1 | 语文 | 97.0 | Jenny |
| 2 | 语文 | 90.0 | Jenny |
| 3 | 语文 | 89.0 | Jenny |
+-------+-------------+-------+---------+
4 rows in set (0.00 sec)
五、控制优化器是否使用derived_merge与derived_condition_pushdown
抛开合并与条件下推的使用限制，MySQL提供了优化器开关与hint两种方式来灵活控制是否使用 derived_merge 与derived_condition_pushdown
1.优化器开关。
set optimizer_switch="derived_merge=on" 启用派生表合并；
set optimizer_switch="derived_merge=off"禁用派生表合并。
set optimizer_switch="derived_condition_pushdown=on" 启用条件推入派生表；
set optimizer_switch="derived_condition_pushdown=off" 禁用条件推入派生表；
2.hint。hint级别的控制优先级高于优化器开关。
/*+ MERGE(derived table name) */ 启用派生表合并
/*+ NO_MERGE(derived table name) */ 禁用派生表合并
/*+ DERIVED_CONDITION_PUSHDOWN(derived table name) */ 启用条件推入派生表
/*+ NO_DERIVED_CONDITION_PUSHDOWN(derived table name) */ 禁用条件推入派生表
六、总结
本文参照官方文档的介绍，以及MySQL不同版本做了大量的实验测试，将derived table的优化处理以及使用限制、注意事项做了一个详细介绍，希望为SQL开发者及优化人员带来一丝帮助。
《深入浅出MGR》视频课程
戳此小程序即可直达B站
https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0
文章推荐：
想看更多技术好文，点个“在看”吧！